package com.dgt.app.OCRDGT;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


public class AlertasDAO {
	

	private String getSQLDeleteUser() {
		String sql="DELETE FROM ALERTAS WHERE USERNAME=? AND DOCNAME=?"; 	
		return sql;
	}

	
	
	public void removeAlertas(Connection conn,String username, String fileName) throws SQLException {
        PreparedStatement stmt = conn.prepareStatement(getSQLDeleteUser());

        stmt.setString(1, username);
        stmt.setString(2, fileName);
        
        stmt.executeQuery();
        stmt.close();
        
		
	}
	
	public void insertarAlertas(Connection conn,String username,String directoryParent,String directoryName,String dateTime, String fileName, String origen, String palabrasClave, int idRole) throws SQLException {
		 String sql = "INSERT INTO ALERTAS (ID_ALERTA,USERNAME,ID_FOLDER,FECHA_ALERTA,DOCNAME,CHECKED,ORIGEN,PALABRASCLAVE,ID_ROLE,UNKNOWN) VALUES (SEQUENCEALERTAS.NEXTVAL,?,(SELECT ID_FOLDER FROM FOLDERS WHERE DIRECTORYPARENT=? AND DIRECTORYNAME=?)," +
	        		" TO_TIMESTAMP_TZ(?, 'DD-MM-YYYY HH24:MI:SS:FF TZR'),?,?,?,?,?,?)";
		 
      PreparedStatement stmt = conn.prepareStatement(sql);

      if(username!=null&&!username.equals(""))
      	stmt.setString(1, username);
      else{
      	stmt.setNull(1, java.sql.Types.NVARCHAR);
      }
      if(directoryParent!=null&&!directoryParent.equals(""))
      	stmt.setString(2, directoryParent);
      else{
      	stmt.setNull(2, java.sql.Types.NVARCHAR);
      }
      if(directoryName!=null&&!directoryName.equals(""))
      	stmt.setString(3, directoryName);
      else{
      	stmt.setNull(3, java.sql.Types.NVARCHAR);
      }
      stmt.setString(4, dateTime);
      
      if(fileName!=null&&!fileName.equals(""))
      	stmt.setString(5, fileName);
		else{
			stmt.setNull(5, java.sql.Types.NVARCHAR);
		}

      stmt.setInt(6, 0);
      stmt.setString(7, origen);
      stmt.setString(8, palabrasClave );
      stmt.setInt(9, idRole);
      stmt.setInt(10, 0);
      stmt.executeQuery();
				   
      stmt.close();
		
	}
	
	public boolean updateAlertaById(Connection conn,Alerta alerta) throws SQLException {
	    
		String sql = "UPDATE ALERTAS SET USERNAME=?,ID_FOLDER=?,FECHA_ALERTA=TO_TIMESTAMP_TZ(?, 'DD-MM-YYYY HH24:MI:SS:FF TZR'),DOCNAME=?,CHECKED=?,ORIGEN=?,PALABRASCLAVE=? WHERE ID_ALERTA=?";
		 
      PreparedStatement stmt = conn.prepareStatement(sql);

      stmt.setString(1, alerta.getUserName());
      stmt.setInt(2, alerta.getIdFolder());
      stmt.setString(3, alerta.getFechaAlerta());
      stmt.setString(4, alerta.getDocName());
      stmt.setInt(5, alerta.isChecked()?1:0);
      stmt.setString(6,alerta.getOrigen());
      stmt.setString(7, alerta.getPalabrasClave());
      stmt.setInt(8, alerta.getIdAlerta());
      int result=stmt.executeUpdate();
				   
      stmt.close();
      
      if(result==0){
      	return false;
      }else
      	return true;
		
	}


	public Alerta getAlertaByPK(Connection conn,String username,String directoryParent,
			String directoryName, String fileName, int idRole) throws SQLException {

		String sql = "SELECT ALERTAS.ID_ALERTA" +
        		" ,ALERTAS.USERNAME" +
        		" ,ALERTAS.ID_FOLDER" +
        		" ,TO_CHAR(ALERTAS.FECHA_ALERTA, 'DD-MM-YYYY HH24:MI:SS:FF TZR') AS FECHA_ALERTA" +
        		" ,ALERTAS.DOCNAME "+
        		" ,ALERTAS.CHECKED "+
        		" ,ALERTAS.UNKNOWN "+
        		" ,ALERTAS.ORIGEN "+
        		" ,ALERTAS.PALABRASCLAVE "+
        		" ,ALERTAS.ID_ROLE ";
		
//		if(directoryParent!=null&&!directoryParent.equals("")&&directoryName!=null&&!directoryName.equals("")){
        		sql+=" ,FOLDERS.FOLDERNAME ";

//		}
        sql+=" FROM ALERTAS" +
	       		" LEFT JOIN FOLDERS ON folders.id_folder=alertas.id_folder "+
        		" WHERE ALERTAS.USERNAME=? AND ALERTAS.ID_FOLDER=(SELECT ID_FOLDER FROM FOLDERS WHERE DIRECTORYPARENT=? AND DIRECTORYNAME=?) AND ALERTAS.DOCNAME=? AND ALERTAS.ID_ROLE=? ";
        
        PreparedStatement stmt = conn.prepareStatement(sql);
        if(username!=null&&!username.equals(""))
        	stmt.setString(1, username);
        else{
        	stmt.setNull(1, java.sql.Types.NVARCHAR);
        }
        if(directoryParent!=null&&!directoryParent.equals(""))
        	stmt.setString(2, directoryParent);
        else{
        	stmt.setNull(2, java.sql.Types.NVARCHAR);
        }
        if(directoryName!=null&&!directoryName.equals(""))
        	stmt.setString(3, directoryName);
        else{
        	stmt.setNull(3, java.sql.Types.NVARCHAR);
        }
        if(fileName!=null&&!fileName.equals(""))
        	stmt.setString(4, fileName);
		else{
			stmt.setNull(4, java.sql.Types.NVARCHAR);
		}
        stmt.setInt(5, idRole);
        
        ResultSet rset = stmt.executeQuery();

        Alerta alerta=null;
        if(rset.next()){
        	alerta=new Alerta();
        	alerta.setIdAlerta(rset.getInt("ID_ALERTA"));
        	alerta.setUserName(rset.getString("USERNAME"));
        	alerta.setFolderName(rset.getString("FOLDERNAME"));
        	alerta.setFechaAlerta(rset.getString("FECHA_ALERTA"));
        	alerta.setIdFolder(rset.getInt("ID_FOLDER"));
        	alerta.setChecked(rset.getInt("CHECKED")==0?false:true);
        	alerta.setUnknown(rset.getInt("UNKNOWN")==0?false:true);
        	alerta.setOrigen(rset.getString("ORIGEN"));
        	alerta.setPalabrasClave(rset.getString("PALABRASCLAVE"));
        	alerta.setDocName(rset.getString("DOCNAME"));
        	alerta.setIdRole(rset.getInt("ID_ROLE"));
        }
        
        rset.close();
        stmt.close();
        
		return alerta;
	}

	public ArrayList<Alerta> getAlertasByDocName(Connection conn,String fileName) throws SQLException {
		ArrayList<Alerta>alertas=new ArrayList<Alerta>();
		String sql = "SELECT ALERTAS.ID_ALERTA" +
        		" ,ALERTAS.USERNAME" +
        		" ,ALERTAS.ID_FOLDER" +
        		" ,TO_CHAR(ALERTAS.FECHA_ALERTA, 'DD-MM-YYYY HH24:MI:SS:FF TZR') AS FECHA_ALERTA" +
        		" ,ALERTAS.DOCNAME "+
        		" ,ALERTAS.CHECKED "+
        		" ,ALERTAS.UNKNOWN"+
        		" ,ALERTAS.ORIGEN "+
        		" ,ALERTAS.PALABRASCLAVE "+
        		" ,ALERTAS.ID_ROLE "+
        		" ,FOLDERS.FOLDERNAME " + 
        		" FROM ALERTAS" +
        		" WHERE ALERTAS.DOCNAME=?";
        
        PreparedStatement stmt = conn.prepareStatement(sql);
        stmt.setString(1, fileName);

        ResultSet rset = stmt.executeQuery();

        Alerta alerta=null;
        while(rset.next()){
        	alerta=new Alerta();
        	alerta.setIdAlerta(rset.getInt("ID_ALERTA"));
        	alerta.setUserName(rset.getString("USERNAME"));
        	alerta.setFolderName(rset.getString("FOLDERNAME"));
        	alerta.setFechaAlerta(rset.getString("FECHA_ALERTA"));
        	alerta.setIdFolder(rset.getInt("ID_FOLDER"));
        	alerta.setChecked(rset.getInt("CHECKED")==0?false:true);
        	alerta.setUnknown(rset.getInt("UNKNOWN")==0?false:true);
        	alerta.setOrigen(rset.getString("ORIGEN"));
        	alerta.setPalabrasClave(rset.getString("PALABRASCLAVE"));
        	alerta.setDocName(rset.getString("DOCNAME"));
        	alerta.setIdRole(rset.getInt("ID_ROLE"));
        	alertas.add(alerta);
        }
        
        rset.close();
        stmt.close();
        
		return alertas;
	}
	
}
